package com.returnsoft.softphone.eao;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;

import javax.ejb.LocalBean;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.NoResultException;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.TypedQuery;

import com.returnsoft.softphone.dto.AgentDto;
import com.returnsoft.softphone.dto.SupervisorDto;
import com.returnsoft.softphone.entity.Agent;
import com.returnsoft.softphone.entity.Peer;
import com.returnsoft.softphone.entity.Server;
import com.returnsoft.softphone.entity.Supervisor;
import com.returnsoft.softphone.exception.EaoExcepcion;

@LocalBean
@Stateless
public class AgentEao implements Serializable {

	private static final long serialVersionUID = 1L;

	@PersistenceContext(name = "CCenterEJB", unitName = "CCenterEJB")
	private EntityManager em;
	
	public String findCallNumber(String idCall, String pbxDatabase) throws EaoExcepcion{
		try {
			String query = 
			"select a.id,q2.source_number from "+pbxDatabase+".queue_log q1 "
			+"left join "+pbxDatabase+".queue_log q2 on q1.call_id=q2.call_id and q2.verb='ENTERQUEUE' "
			+"left join ccenter.agent a on q2.login_user=a.code "
			+"where q1.call_id='"+idCall+"' and q1.verb='CONNECT'";
			
			Query q = em.createNativeQuery(query);

			Object[] numberObject = (Object[]) q.getSingleResult();
			
			String respuesta = "";

			if (numberObject.length > 0) {
					Integer agentId = (Integer) numberObject[0];
					String agentCallNumber = (String) numberObject[1];
					respuesta = agentId+":"+agentCallNumber; 
				
			}
			
			return respuesta;
			
		} catch (Exception e) {
			e.printStackTrace();
			throw new EaoExcepcion(e.getMessage());
		}
		
	}

	public Agent addAgent(Agent agent) throws EaoExcepcion {
		try {
			agent = em.merge(agent);
			em.flush();
			return agent;
		} catch (Exception e) {
			e.printStackTrace();
			throw new EaoExcepcion(e.getMessage());
		}
	}

	public Agent findAgent(Integer userId) throws EaoExcepcion {

		try {
			TypedQuery<Agent> q = em.createQuery("SELECT a " + "FROM Agent a "
					+ "WHERE a.userId = :userId", Agent.class);

			q.setParameter("userId", userId);
			Agent agent = q.getSingleResult();
			return agent;
		} catch (NoResultException e) {
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			throw new EaoExcepcion(e.getMessage());
		}

	}

	public Agent findAgentById(Integer agentId) throws EaoExcepcion {

		try {
			TypedQuery<Agent> q = em.createQuery(
					"SELECT a FROM Agent a WHERE a.id = :agentId", Agent.class);
			q.setParameter("agentId", agentId);
			Agent agent = q.getSingleResult();
			return agent;

		} catch (NoResultException e) {
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			throw new EaoExcepcion(e.getMessage());
		}

	}

	

	@SuppressWarnings("unchecked")
	public List<AgentDto> findAgents(List<Integer> campaignsId,
			List<Integer> supervisorsId, String code, String username,
			String documentNumber, String name, Boolean autoready,
			Boolean isActive, Boolean supervisorNull,Boolean campaignNull) throws EaoExcepcion {

		try {

			String query = "select a.id,a.code,au.username,ap.document_number, "
					+ "ap.firstname,ap.lastname, "
					+ "sp.firstname,sp.lastname, "
					+ "group_concat(c.name separator ' - ') as campaigns, "
					+ "a.autoready,au.is_active "
					+ "from ccenter.agent a "
					+ "left join ccenter.agent_campaign ac on ac.agent_id=a.id "
					+ "left join ccenter.campaign c on c.id=ac.campaign_id "
					+ "left join ccenter.supervisor s on s.id=a.supervisor_id "
					+ "left join security.user au on au.id=a.user_id "
					+ "left join security.person ap on ap.id=au.person_id "
					+ "left join security.user su on su.id=s.user_id "
					+ "left join security.person sp on sp.id=su.person_id "
					+ "where a.id>0 ";

			if (campaignsId != null && campaignsId.size() > 0) {
				query += " and ac.campaign_id in ";
				query += " ( ";
				for (int i = 0; i < campaignsId.size(); i++) {
					Integer campaignId = campaignsId.get(i);
					if (i < campaignsId.size() - 1) {
						query += campaignId + ",";
					} else {
						query += campaignId;
					}
				}
				query += " ) ";
			} else {
				if (campaignNull) {
					query += " and ac.campaign_id is null";	
				}
			}

			if (supervisorsId != null && supervisorsId.size() > 0) {
				query += " and a.supervisor_id in ";
				query += " ( ";
				for (int i = 0; i < supervisorsId.size(); i++) {
					Integer supervisorId = supervisorsId.get(i);
					if (i < supervisorsId.size() - 1) {
						query += supervisorId + ",";
					} else {
						query += supervisorId;
					}
				}
				query += " ) ";
			} else {
				if (supervisorNull) {
					query += " and a.supervisor_id is null";	
				}
			}

			if (code != null && !code.equals("")) {
				query += " and a.code like '%" + code + "%'";
			}
			
			if (username != null && !username.equals("")) {
				query += " and au.username like '%" + username + "%'";
			}
			
			if (documentNumber != null && !documentNumber.equals("")) {
				query += " and ap.document_number like '%" + documentNumber + "%'";
			}
			
			if (name != null && !name.equals("")) {
				query += " and (ap.firstname like '%" + name + "%' or ap.lastname like '%" + name + "%')";
			}
			
			if (autoready != null) {
				query += " and a.autoready = " + autoready;
			}
			
			if (isActive != null) {
				query += " and au.is_active = " + isActive;
			}


			query += " group by a.id";

			// System.out.println(query);

			Query q = em.createNativeQuery(query);

			

			List<Object[]> userObjectList = (List<Object[]>) q.getResultList();

			List<AgentDto> agents = new ArrayList<AgentDto>();
			if (userObjectList.size() > 0) {
				for (Object[] userObject : userObjectList) {

					Integer agentId = (Integer) userObject[0];
					String agentCode = (String) userObject[1];
					String agentUsername = (String) userObject[2];
					String agentDocumentNumber = (String) userObject[3];
					String agentFirstname = (String) userObject[4];
					String agentLastname = (String) userObject[5];
					String supervisorFirstname = (String) userObject[6];
					String supervisorLastname = (String) userObject[7];
					String agentCampaigns = (String) userObject[8];
					Boolean agentAutoready = (Boolean) userObject[9];
					Boolean agentIsActive = (Boolean) userObject[10];

					AgentDto agent = new AgentDto();
					agent.setId(agentId);
					agent.setCode(agentCode);
					agent.setUsername(agentUsername);
					agent.setDocumentNumber(agentDocumentNumber);
					agent.setFirstname(agentFirstname);
					agent.setLastname(agentLastname);
					SupervisorDto supervisor = new SupervisorDto();
					supervisor.setFirstname(supervisorFirstname);
					supervisor.setLastname(supervisorLastname);
					agent.setSupervisor(supervisor);
					agent.setCampaignsList(agentCampaigns);
					agent.setAutoready(agentAutoready);
					agent.setIsActive(agentIsActive);

					/*
					 * agent.setUserId(agentUserId);
					 * agent.setAutoready(agentAutoready); Supervisor supervisor
					 * = new Supervisor(); supervisor.setId(supervisorId);
					 * supervisor.setCode(supervisorCode);
					 * agent.setSupervisor(supervisor);
					 */

					agents.add(agent);
				}
			}
			return agents;
		} catch (Exception e) {
			e.printStackTrace();
			throw new EaoExcepcion(e.getMessage());
		}
	}

}
